from xeniatools.xenia import dbTypes
from xeniatools.xenia import xeniaSQLite
from xeniatools.xenia import xeniaPostGres
from xeniatools.xenia import uomconversionFunctions
from xeniatools.xmlConfigFile import xmlConfigFile
import optparse
import sys, os 
                
if __name__ == '__main__':
    #strOutput = "<platformList>"
    strOutput = ""
    parser = optparse.OptionParser()
    parser.add_option("-c", "--XMLConfigFile", dest="xmlConfigFile",
                      help="XML Configuration file." )
    parser.add_option("-b", "--bbox", dest="bbox",
                      help="Bounding box used to select platforms." )
    parser.add_option("-m", "--XMLFileName", dest="XMLFileName",
                      help="Filename to create XML File" )
    parser.add_option("-j", "--jsonfilename", dest="jsonfilename",
                      help="Filename to create JSON File" )
    (options, args) = parser.parse_args()
    
    configSettings = xmlConfigFile( options.xmlConfigFile )
    
    converFile = configSettings.getEntry('//environment/unitsConversion/file')
    print("Units conversion file: %s" %(converFile))
    unitsConverter = uomconversionFunctions(converFile)
    try:
        dbSettings = configSettings.getDatabaseSettings()
        dbType = dbTypes.undefined
        if( dbSettings['type'] == 'sqlite' ):       
          db = xeniaSQLite()
          if( db.connect( dbSettings['dbName'] ) == False ):
            print( "Unable to connect to xeniaDB: %s\nError: %s" % (dbSettings['dbName'],db.lastErrorMsg) )
            sys.exit(-1)
          dbType = dbTypes.SQLite
          #Load the SpatiaLite extension.       
          lib = configSettings.getEntry("//environment/database/db/spatiaLiteLib")
          if(lib != None):
            db.loadSpatiaLiteLib(lib)
          else:
            print( "\\environment\database\db\spatiaLiteLib entry does not exist." )
            sys.exit(-1)
        else:
          db = xeniaPostGres()
          if( db.connect( None, 
                          dbSettings['dbUser'],
                          dbSettings['dbPwd'],
                          dbSettings['dbHost'],
                          dbSettings['dbName']
                         ) == False ):
            print( "Unable to connect to xeniaDB: %s Host: %s User: %s\nError: %s" % (dbSettings['dbName'], dbSettings['dbHost'], dbSettings['dbUser'] ,db.lastErrorMsg) )
            sys.exit(-1)
          dbType = dbTypes.PostGRES
        
        #dbCursor = DB.cursor()
        strSQL = "SELECT platform_handle, description, fixed_longitude, fixed_latitude, url\
                 FROM platform\
                 WHERE active = 1 AND\
                 Contains( GeomFromText( \'POLYGON(("+ options.bbox +"))\'), GeomFromText( 'POINT(' || fixed_longitude || ' ' || fixed_latitude ||')' ) )\
                 ORDER BY platform_handle ASC"
        dbCursor = db.executeQuery( strSQL )
        if(dbCursor == None):
          print( db.lastErrorMsg )
          sys.exit(-1)
        bWriteFile = 0
        outFile = 0
        #if( len( options['XMLFileName'] ) ):
        #  bWriteFile = 1
        #  writeXML = 1
        #  outFile = open( options['XMLFileName'], 'w' )
        #  outFile.write( "<platformList>" );
        if( len( options.jsonfilename ) ):
          bWriteFile = 1
          outFile = open( options.jsonfilename, 'w' )
          outFile.write( "{\n\"platformList\":{ \n" )
          outFile.write( "\"platform\":[\n" )

        else:
          strOutput = "<platformList>"
        rowCnt = 0
        for row in dbCursor:
          if( rowCnt > 0 ):
            outFile.write( ',\n' );
            
          if( bWriteFile == 0 ):           
            strOutput = strOutput + '<platform name=\"' + row['platform_handle'] + '\">'  
          else:
            outFile.write( '{\n' )
            outFile.write( "\"name\":\"%s\",\n" % row['platform_handle'] )
          
          desc = row['description']
          if(desc != None):
          #if( 'description' in row != False ):
            if( bWriteFile == 0 ):
              strOutput = strOutput + '<desc>' + desc + '</desc>'  
            else:
              outFile.write( "\"desc\":\"%s\",\n" % desc  )
          
          url = row['url']
          if(url != None):
          #if( 'url' in row != False ):
            if( bWriteFile == 0 ):
              strOutput = strOutput + '<url>' + url + '</url>'  
            else:
              outFile.write( "\"url\":\"%s\",\n" % url )
          #Get the observations for the platform that have updated at least in the last 6 hours.
          if(dbType == dbTypes.SQLite):            
            sql = "SELECT multi_obs.m_value, obs_type.standard_name, uom_type.standard_name AS uom, max(multi_obs.m_date),multi_obs.m_type_id FROM multi_obs \
                 LEFT JOIN  m_type on multi_obs.m_type_id = m_type.row_id \
                 LEFT JOIN m_scalar_type on m_scalar_type.row_id = m_type.m_scalar_type_id \
                 LEFT JOIN obs_type on  obs_type.row_id = m_scalar_type.obs_type_id \
                 LEFT JOIN  uom_type on uom_type.row_id = m_scalar_type.uom_type_id \
                 WHERE \
                   m_date >= datetime('now','-6 hours') AND \
                   platform_handle = '%s'\
                 GROUP BY obs_type.standard_name \
                ORDER BY obs_type.standard_name ASC" \
                %( row['platform_handle'] )
          else:
            print(row['platform_handle'])
            sql = "SELECT obs_type.standard_name, max(multi_obs.m_date),uom_type.standard_name AS uom,multi_obs.m_type_id FROM multi_obs \
                 LEFT JOIN  m_type on multi_obs.m_type_id = m_type.row_id \
                 LEFT JOIN m_scalar_type on m_scalar_type.row_id = m_type.m_scalar_type_id \
                 LEFT JOIN obs_type on  obs_type.row_id = m_scalar_type.obs_type_id \
                 LEFT JOIN  uom_type on uom_type.row_id = m_scalar_type.uom_type_id \
                 WHERE \
                   m_date >= timestamp 'now' + interval '-6 hours' AND \
                   platform_handle = '%s'\
                 GROUP BY obs_type.standard_name,uom_type.standard_name,multi_obs.m_type_id \
                ORDER BY obs_type.standard_name ASC" \
                %( row['platform_handle'] )
          obsCursor = db.executeQuery( sql )
          if(obsCursor == None):
            print( db.lastErrorMsg )
            sys.exit(-1)
            
          if( bWriteFile == 0 ):
            strOutput = strOutput + "<obslist>"
          else:
            outFile.write( "\"obslist\":{\n" )

          outBuf = ''
          for obsRow in obsCursor:
            obsName = obsRow['standard_name']
            m_type_id = obsRow['m_type_id']
            uom = obsRow['uom']
            if( bWriteFile == 0 ):
              strOutput = strOutput + "<%s>%d</%s>" % (obsName,m_type_id,obsName)
            else:
              if( len(outBuf)):
                outBuf += ','
              obsName = obsName.replace('_',' ')
              #outBuf += "\"%s\":\"%d\"\n" % (obsName,m_type_id)
              outBuf += "\"%s\":{\n" % ( obsName )
              outBuf += "\"m_type_id\":\"%d\",\n" %(m_type_id)
              enUom = unitsConverter.getConversionUnits( uom, 'en' )
              if( len(enUom) ):
                uom = enUom
              outBuf += "\"uom\":\"%s\"\n}\n" %(uom)
              
          outFile.write( outBuf )
          if( bWriteFile == 0 ):
            strOutput = strOutput + "}\n"
          else:
            outFile.write( "}\n" )
                       
          if( bWriteFile == 0 ):
            strOutput = strOutput + '</platform>';  
          else:
            outFile.write( '}\n' );
          rowCnt += 1
        if( bWriteFile == 0 ):
          strOutput = strOutput + "</platformList>"
          print( strOutput )
        else:
          outFile.write( ']\n}\n}\n' )
          outFile.close()

    except Exception, E:
      import sys
      import traceback
      
      info = sys.exc_info()        
      excNfo = traceback.extract_tb(info[2],1)
      items = excNfo[0]
      print( 'ERROR: %s File: %s Line: %s Function: %s' % (str(E),items[0],items[1],items[2]))

    sys.exit(1)